#!/usr/bin/python
# -*- coding: utf-8 -*-

from odbc import ODBC
from util.logger import process_log
from util.tools import calculate_date_day
import fdb
import os

__author__ = 'xiwei'


class FIREBIRD(ODBC):

    # sys_db = ['master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB']

    def text_convert(self, table, tb_id, field, field_id, delta, fmt):
        max_value = self.read_index(tb_id, field_id)
        max_value = calculate_date_day(max_value, fmt, delta)
        if not max_value:
            return ''
        where = '''where "%s" >= "%s"''' % (field, max_value)
        return where

    def dump(self, database_name, table_name, fields_name, where, tb_id, return_cursor=False, breakpoint=-1):
        fields_with_table = map(lambda x: self.field_join(x[0], x[1]), [(table_name, field) for field in fields_name])
        fields_sql = ', '.join(fields_with_table)
        sql = '''select %s from "%s" %s ''' % (fields_sql, table_name, where)
        cursor = self.cursor

        if return_cursor:
            process_log().info("Fetch data by sql: %s" % sql)
            cursor.execute(sql)
            return cursor

    def count(self, database, table, where):
        """
            Get the count of sync by giving info
        Args:
            database (str):
            table (str):
            where (str):

        Returns:
            sql_count (int):
        """
        sql = 'select count(*) from "%s" %s' % (table, where)
        process_log().info('Fetch rows count by sql statement: %s' % sql)
        self.cursor.execute(sql)
        res = self.cursor.fetchall()
        return str(res[0][0]) if res else '0'

    def max_value(self, database, table, index, where):
        sql = 'select max("%s"."%s") ' \
              'from "%s" %s' % (table, index, table, where)
        process_log().info('Fetch max value by sql: %s' % sql)
        self.cursor.execute(sql)
        res = self.cursor.fetchall()
        return str(res[0][0]) if res else ''

    def field_join(self, table, field):
        raw_type = self.field_dict.get(table, {}).get(field, {}).get('raw_type', '')
        if raw_type == 'float':
            return u'"%s"."%s"' % (table, field)
        else:
            return u'"%s"."%s"' % (table, field)

    def fetch_schema(self, database, table_name):
        sql = """
        select fd.tb_name, fd.field_name, fd.type, pk.pri from
            (
                select r.tb as tb_name, r.fname as field_name, rdb$field_type as type
                from rdb$fields f
                join (
                        select rdb$field_name as fname, rdb$field_source, rdb$relation_name as tb
                        from rdb$relation_fields
                        where rdb$relation_name = '%s'
                    ) r
                on r.rdb$field_source = f.rdb$field_name
            ) fd
            left join
            (
                select
                    s.rdb$field_name as name,
                    'PRI' as pri
                from
                    rdb$indices i
                left join
                    (select * from rdb$index_segments) s
                on i.rdb$index_name = s.rdb$index_name
                left join
                    (select *
                        from rdb$relation_constraints
                        where rdb$relation_name = '%s') rc
                on rc.rdb$index_name = i.rdb$index_name
                where
                    rc.rdb$constraint_type = 'PRIMARY KEY'
            ) pk
            on pk.name = fd.field_name
        """ % (table_name, table_name)
        self.cursor.execute(sql)
        cols = [(self.blank_replace(r[0]), self.blank_replace(r[1]), r[2], r[3]) for r in self.cursor.fetchall()]
        return cols

    def __init__(self, connect_info):
        ODBC.__init__(self, connect_info)
        os.environ['PATH'] = os.path.dirname('lib\\firebird\\fbclient.dll') + ';' + os.environ['PATH']
        # ODBC.__init__(self, connect_info)
        self.cursor = fdb.connect(database=connect_info['server'],
                                  user=connect_info['uid'],
                                  password=connect_info['pwd'],
                                  fb_library_name='lib\\firebird\\fbclient.dll').cursor()
        self.database = connect_info.get('server')

    def fetch_databases(self):
        return [(self.database,)]

    def fetch_tables(self, database, view=True):
        sql = """
            select rdb$relation_name
            from rdb$relations
            where (rdb$system_flag is null or rdb$system_flag = 0)
        """
        self.cursor.execute(sql)
        cols = [(self.blank_replace(r[0]), ) for r in self.cursor.fetchall()]
        return cols

    def fetch_fields(self, database, table):
        sql = '''
            select r.fname as field_name, rdb$field_type from rdb$fields f
            join (select rdb$field_name as fname, rdb$field_source
                    from rdb$relation_fields
                    where rdb$relation_name = '%s') r
            on r.rdb$field_source = f.rdb$field_name
        ''' % table
        self.cursor.execute(sql)
        cols = [(self.blank_replace(r[0]), r[1]) for r in self.cursor.fetchall()]
        return cols

    def fetch_record_count(self, database, table):
        sql = '''select count(1) from "%s"''' % table
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    @staticmethod
    def blank_replace(str):
        while str.endswith(' '):
            str = str[:-1]
        return str


if __name__ == '__main__':
    # connect_info = {
    #     'server': 'C:\\fb.fdb',
    #     'uid': 'SYSDBA',
    #     'pwd': 'masterkey'
    # }
    # db = FIREBIRD(connect_info)

    # tables = db.fetch_tables('C:\\fb.fdb')
    # print db.fetch_fields('sd', 'sd')
    # # print tables
    pass
